package com.solo.dao;

import com.solo.entity.User;
import com.solo.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    /**
     * 新增用户
     *
     * @param user
     * @return
     */
    public boolean addUser(User user) {
        boolean state = false;
        conn = DBUtil.getConn();
        try {
            String sql = "insert into `sys_user`(username,password,name,email,phonenumber,gender) values(?, ?, ?, ?, ?, ?)";
            // String sql = "insert into `sys_user`(username,password) values(?, ?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUsername());
            pst.setString(2, user.getPassword());
            pst.setString(3, user.getName());
            pst.setString(4, user.getEmail());
            pst.setString(5, user.getPhonenumber());
            pst.setString(6, user.getGender());

            int index = pst.executeUpdate();
            if (index > 0) {
                state = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return state;
    }

    /**
     * 查询所有用户
     *
     * @return
     * @throws Exception
     */
    public List<User> selectAllUser() {
        conn = DBUtil.getConn();
        List<User> listUser = new ArrayList<>();
        try {
            String sql = "SELECT * FROM sys_user";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            // 也可以使用createStatement()
            // Statement statement = conn.createStatement();
            // ResultSet resultSet = statement.executeQuery(sql);

            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String phonenumber = rs.getString("phonenumber");
                String gender = rs.getString("gender");
                // 调用构造方法实例化User对象
                User User = new User(id, username, password,name,email,phonenumber,gender);
                listUser.add(User);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return listUser;
    }

    /**
     * 删除用户
     * @param User
     * @return
     */
    public boolean deleteUser(User User) {
        boolean rowDeleted = false;
        conn = DBUtil.getConn();
        try {
            String sql = "DELETE FROM sys_user where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, User.getId());

            rowDeleted = pst.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return rowDeleted;
    }

    /**
     * 更新用户信息
     * @param user
     * @return
     */
    public boolean updateUser(User user) {
        boolean rowUpdated = false;
        conn = DBUtil.getConn();

        try {
            String sql = "UPDATE sys_user SET username = ?, password = ?,name = ?,email = ?,phonenumber = ?,gender = ?";
            sql += " WHERE id = ?";

            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUsername());
            pst.setString(2, user.getPassword());
            pst.setString(3, user.getName());
            pst.setString(4, user.getEmail());
            pst.setString(5, user.getPhonenumber());
            pst.setString(6, user.getGender());
            pst.setLong(7, user.getId());
            System.out.println("sql=="+sql);
            rowUpdated = pst.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return rowUpdated;
    }

    /**
     * 查询登录用户
     * @param username
     * @param password
     * @return
     */
    public User selectLoginUser(String username, String password) {
        User user = null;
        conn = DBUtil.getConn();
        try {
            conn.setAutoCommit(false);
            pst = conn.prepareStatement("select * from sys_user where username=? and password=?");
            pst.setString(1, username);
            pst.setString(2, password);
            rs = pst.executeQuery();
            if (rs.next()) {
                Long id = rs.getLong("id");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                user = new User(id, username1, password1);
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return user;
    }

    /**
     * 按id号查询用户
     * @param id
     * @return
     */
    public User selectUserById(Long id) {
        conn = DBUtil.getConn();
        User user = null;
        try {
            String sql = "SELECT * FROM sys_user WHERE id = ?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, id);
            rs = pst.executeQuery();
            if (rs.next()) {
                String username = rs.getString("username");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String phonenumber = rs.getString("phonenumber");
                String gender = rs.getString("gender");
                user = new User(id, username, password,name,email,phonenumber,gender);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }

        return user;
    }

    public List<User> selectUserByUsername(String username) {

        conn = DBUtil.getConn();
        User user = null;
        List<User> listUser = new ArrayList<>();
        try {
            String sql = "SELECT * FROM sys_user WHERE username  like ?";
            pst = conn.prepareStatement(sql);
            System.out.println("username===" + username);
            pst.setString(1, "%"+username+"%");

            rs = pst.executeQuery();
            while (rs.next()){
                Long id = rs.getLong("id");
                String username1 = rs.getString("username");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String phonenumber = rs.getString("phonenumber");
                String gender = rs.getString("gender");
                user = new User(id,username1, password,name,email,phonenumber,gender);
                listUser.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }

        return listUser;
    }
}
